package com.liuke.study.jdbc;

import java.sql.*;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * TODO
 *
 * @author wangjwy
 * @date 2019/10/8 10:19
 * @since 1.0.0
 */
public class Compare {

	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection con = null;
		Connection con2 = null;
		try {
			//通过驱动管理器获取连接---三个参数：URL, 用户名、密码
			//URL---统一资源定位符---格式如： http://192.168.5.12:9527/index.html
			//                         协议 :// 对方机器的IP地址:端口号/资源名
			String port = "3306";
			String host = "172.29.3.205";
			String userName = "yjsdata";
			String password = "PtSjKmMkFwY666";
			String scheme = "yyfax_pay";
			con = DriverManager.getConnection("jdbc:mysql://"+ host+":"+ port +"/"+ scheme +"?relaxAutoCommit=true&useUnicode=true&characterSetResults=UTF-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&failOverReadOnly=false&useSSL=false", userName, password);
			Map<String, List<Field>> firstMap = obtainMap(con);


			String port2 = "3314";
			String host2 = "172.29.2.238";
			String userName2 = "yjsdata";
			String password2 = "yjsdata_123%";
			String scheme2 = "yyfax_pay";
			con2 = DriverManager.getConnection("jdbc:mysql://"+ host2+":"+ port2 +"/"+ scheme2 +"?relaxAutoCommit=true&useUnicode=true&characterSetResults=UTF-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&useSSL=false", userName2, password2);
			Map<String, List<Field>> secondMap = obtainMap(con2);
			System.out.println();
			System.out.println();
			System.out.println("*******************************************************************************************************************************");
			System.out.println("test: " + host + ":" + port + "   " + scheme);
			System.out.println("prod: " + host2 + ":" + port2 + "   " + scheme2);
			// System.out.println("*******************************************************************************************************************************");
			compare(firstMap, secondMap, con2);

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			/*
			 * 关闭连接
			 */
			if(con != null){
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}

	}

	private static void compare(Map<String, List<Field>> firstMap, Map<String, List<Field>> secondMap, Connection connection) {
		List<String> lines = new ArrayList<>();

		List<String> firstMoreTable = new ArrayList<>();
		List<String> secondMoretable = new ArrayList<>();
		List<String> diffTable = new ArrayList<>();
		for (Map.Entry<String, List<Field>> entry : firstMap.entrySet()) {
			List<Field> firstMoreField = new ArrayList<>();
			List<Field> secondMoreField = new ArrayList<>();

			List<Field> diffFields = new ArrayList<>();
			String key = entry.getKey();
			List<Field> value = entry.getValue();
			List<Field> fields = secondMap.get(key);
			if (null == fields) {
				firstMoreTable.add(key);
				continue;
			}
			Map<String, Field> first = value.stream().collect(Collectors.toMap(Field::getField, Function.identity()));
			Map<String, Field> second = fields.stream().collect(Collectors.toMap(Field::getField, Function.identity()));
			for (Map.Entry<String, Field> fieldEntry : first.entrySet()) {
				String fieldEntryKey = fieldEntry.getKey();
				Field fieldEntryValue = fieldEntry.getValue();
				Field field = second.get(fieldEntryKey);
				if (null == field) {
					firstMoreField.add(fieldEntryValue);
					continue;
				}
				if (!field.equals(fieldEntryValue)){
					diffFields.add(field);
					diffFields.add(fieldEntryValue);
				}
				second.remove(fieldEntryKey);
			}

			for (Map.Entry<String, Field> secondEntry : second.entrySet()) {
				secondMoreField.add(secondEntry.getValue());
			}
			secondMap.remove(key);
			Boolean flag = false;

			if (firstMoreField.size() != 0) {
				System.out.println("###############################################################################################################################");
				System.out.println( "表: " + key + " test多出的字段:");
				for (Field field : firstMoreField) {
					System.out.println(field);
				}
				System.out.println("###############################################################################################################################");
				flag = true;
			}

			if (secondMoreField.size() != 0) {
				System.out.println("###############################################################################################################################");
				System.out.println( "表: " + key + " prod多出的字段:");
				for (Field field : secondMoreField) {
					System.out.println(field);
				}
				System.out.println("###############################################################################################################################");
				flag = true;
			}

			if (diffFields.size() != 0) {
				System.out.println("###############################################################################################################################");
				System.out.println( "表: " + key + " 差异字段:");
				for (Field diffField : diffFields) {
					System.out.println(diffField);
				}
				System.out.println("###############################################################################################################################");
				flag = true;
			}

			if (flag) {
				// lines.add("drop table if exists " + key + ";");
				// lines.add(obtainCreateSql(connection, key));
				diffTable.add(key);
			}

		}

		for (Map.Entry<String, List<Field>> stringListEntry : secondMap.entrySet()) {
			secondMoretable.add(stringListEntry.getKey());
		}

		if (firstMoreTable.size() != 0) {
			System.out.println("###############################################################################################################################");
			System.out.println("test多出的表:");
			for (String table : firstMoreTable) {
				System.out.println(table);
			}
			System.out.println("###############################################################################################################################");
		}

		if (secondMoretable.size() != 0) {
			System.out.println("###############################################################################################################################");
			System.out.println("prod多出的表:");
			for (String table : secondMoretable) {
				System.out.println(table);
			}
			System.out.println("###############################################################################################################################");
		}

		if (diffTable.size() != 0) {
			System.out.println("###############################################################################################################################");
			System.out.println("存在差异的表:");
			for (String s : diffTable) {
				System.out.println(s);
			}
			System.out.println("###############################################################################################################################");
		}

		for (String line : lines) {
			System.out.println(line);
		}
	}

	private static String obtainCreateSql(Connection connection, String key) {
		String sql = "show create table " + key + ";";
		Statement statement = null;
		try {
			statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(sql);
			if (resultSet.next()) {
				return resultSet.getString(2) + ";";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		throw new RuntimeException();
	}

	private static Map<String, List<Field>> obtainMap(Connection con) throws Exception {
		HashSet<String> tables = obtainTables(con);
		return obtainTableMap(tables, con);
	}


	private static Map<String, List<Field>> obtainTableMap(HashSet<String> tables, Connection con) throws Exception {
		Map<String, List<Field>> map = new HashMap<>();
		for (String table : tables) {
			String sql = "desc " + table + "; ";
			/*
			 * 利用连接创建获取语句对象--Statement
			 */
			Statement stat = con.createStatement();

			/*
			 * 语句对象执行SQL
			 */
			ResultSet resultSet = stat.executeQuery(sql);
			List<Field> fields = new ArrayList<>();
			while (resultSet.next()){
				Field field = new Field();
				field.setField(resultSet.getString(1));
				field.setType(resultSet.getString(2));
				field.setIsnull(resultSet.getString(3));
				field.setKey(resultSet.getString(4));
				field.setDefaultValue(resultSet.getString(5));
				field.setExtra(resultSet.getString(6));
				fields.add(field);
			}
			map.put(table, fields);
		}
		return map;
	}

	private static HashSet<String> obtainTables(Connection con) throws SQLException {
		/*
		 * 书写sql语句
		 */
		String sql = "show full tables where table_type = 'BASE Table';";


		/*
		 * 利用连接创建获取语句对象--Statement
		 */
		Statement stat = con.createStatement();

		/*
		 * 语句对象执行SQL
		 */
		ResultSet resultSet = stat.executeQuery(sql);
		HashSet<String> tables = new HashSet<>();
		while(resultSet.next()){
			tables.add(resultSet.getString(1));
		}
		return tables;
	}

}


class Field {

	private String field;

	private String type;

	private String isnull;

	private String key;

	private String defaultValue;

	private String extra;

	@Override
	public boolean equals(Object o) {
		if (this == o){
			return true;
		}
		if (!(o instanceof Field)){
			return false;
		}
		Field field1 = (Field) o;
		return Objects.equals(getField(), field1.getField()) && Objects.equals(getType(), field1.getType()) && Objects
				.equals(getIsnull(), field1.getIsnull()) && Objects.equals(getKey(), field1.getKey()) && Objects
				.equals(getDefaultValue(), field1.getDefaultValue()) && Objects.equals(getExtra(), field1.getExtra());
	}

	@Override
	public String toString() {
		// return field + "," + type + "," + (isnull.equals("NO")?"NOT NULL":"NULL") + "," + key
		// 		+ "," + defaultValue + "," + extra;

		return "name='" + field + '\'' + ", type='" + type + '\'' + ", nullable='" + (isnull.equals("NO")?"NOT NULL":"NULL") + '\'' + ", key='" + key + '\''
				+ ", defaultValue='" + defaultValue + '\'' + ", extra='" + extra + '\'';
	}

	@Override
	public int hashCode() {
		return Objects.hash(getField(), getType(), getIsnull(), getKey(), getDefaultValue(), getExtra());
	}

	public String getField() {
		return field;
	}

	public void setField(String field) {
		this.field = field;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public String getIsnull() {
		return isnull;
	}

	public void setIsnull(String isnull) {
		this.isnull = isnull;
	}

	public String getKey() {
		return key;
	}

	public void setKey(String key) {
		this.key = key;
	}

	public String getDefaultValue() {
		return defaultValue;
	}

	public void setDefaultValue(String defaultValue) {
		this.defaultValue = defaultValue;
	}

	public String getExtra() {
		return extra;
	}

	public void setExtra(String extra) {
		this.extra = extra;
	}
}